package com.jp.fm.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import net.sf.ehcache.Cache;
import net.sf.ehcache.Element;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.jasypt.util.text.BasicTextEncryptor;

import com.jp.fm.HomeFinanceRunner;
import com.jp.fm.data.Account;
import com.jp.fm.data.Account.AccountBalance;
import com.jp.fm.data.AccountType;
import com.jp.fm.data.Institution;
import com.jp.fm.data.Site;
import com.jp.fm.data.UserAccount;

public class AccountDao {

	private static final int DAYS_IN_WEEK = 7;
	private static final int MINUTES_TO_WAIT_ON_ERROR_REFRESH = 61;
	// Needs to be less than error refresh interval
	private static final int MINUTES_TO_WAIT_ON_REFRESH = 60;
	private static final String SELECT_COLUMNS = "select a.id, a.name, a.institution_id, a.account_number, "
			+ "a.account_type, a.balance_id, a.total_credit, a.available_credit, "
			+ "a.lastupdate, a.sequence, a.last_failure_date, a.hidden from account a";
	private static final String COLUMN_AVAILABLE_CREDIT = "AVAILABLE_CREDIT";
	private static final String COLUMN_TOTAL_CREDIT = "TOTAL_CREDIT";
	private static final String COLUMN_BALANCE_ID = "BALANCE_ID";
	private static final String COLUMN_BALANCE = "BALANCE";
	private static final String COLUMN_ACCOUNT_TYPE = "ACCOUNT_TYPE";
	private static final String COLUMN_ACCOUNT_NUMBER = "ACCOUNT_NUMBER";
	private static final String COLUMN_ACCOUNT_HIDDEN = "HIDDEN";
	private static final String COLUMN_INSTITUTION_ID = "INSTITUTION_ID";
	private static final String COLUMN_LASTUPDATE = "LASTUPDATE";
	private static final String COLUMN_LAST_FAILURE_DATE = "LAST_FAILURE_DATE";
	private static final String COLUMN_NAME = "NAME";
	private static final String COLUMN_ID = "ID";
	private static final String COLUMN_DATE = "DATE";
	private static final String COLUMN_SEQUENCE = "SEQUENCE";

	private static final String UPDATE_SQL_WITH_BALANCE = "update account set balance = ?, "
			+ "available_credit = ?, balance_id = ?, lastupdate = CURRENT_TIMESTAMP, last_failure_date = ?, "
			+ "hidden = ? where name = ?";

	private static final String INSERT_ACCOUNT_BALANCE_SQL = "insert into account_balance (account, balance, date) "
			+ "VALUES(?,?,CURRENT_TIMESTAMP)";

	public static final String CACHE_NAME = "Account";

	public List<Account> findAll() {

		List<Account> accts = new ArrayList<Account>();
		Account acct = null;

		// look in cache first
		Cache cache = HomeFinanceRunner.getCache(CACHE_NAME);
		Map<Object, Element> elementMap = cache.getAll(cache
				.getKeysWithExpiryCheck());
		if (null == elementMap || elementMap.size() == 0) {

			Connection c = null;
			PreparedStatement p = null;
			ResultSet rs = null;

			try {
				c = HomeFinanceRunner.getConnection();

				p = c.prepareStatement(SELECT_COLUMNS);

				rs = p.executeQuery();

				while (rs.next()) {
					acct = createAccount(rs);

					accts.add(acct);
					cache.put(new Element(acct.getName(), acct));
				}

			} catch (SQLException e) {

				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				HomeFinanceRunner.closeConnection(c, p, rs);
			}
		} else {
			for (Iterator<Element> iterator = elementMap.values().iterator(); iterator
					.hasNext();) {

				Element element = iterator.next();

				Object obj = element.getObjectValue();
				if (Account.class.equals(obj.getClass())) {
					acct = (Account) obj;

					// Find out if this account can be refreshed
					acct.setCanRefresh(determineIfCanRefresh(acct));
					accts.add(acct);
				}
			}

		}

		Collections.sort(accts);
		return accts;
	}

	public Float findAverageAccountBalanceForRange(final Date startDate,
			final Date endDate, final Account acct) {

		Float ret = Float.valueOf(NumberUtils.FLOAT_ZERO);

		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;

		try {
			c = HomeFinanceRunner.getConnection();

			p = c.prepareStatement("select balance from account_balance where "
					+ "date >= ? and date <= ? and account = ?");

			p.setDate(HomeFinanceRunner.DB_COLUMN_FIRST, new java.sql.Date(
					startDate.getTime()));
			p.setDate(HomeFinanceRunner.DB_COLUMN_SECOND, new java.sql.Date(
					endDate.getTime()));
			p.setString(HomeFinanceRunner.DB_COLUMN_THIRD, acct.getName());
			rs = p.executeQuery();

			int rowCount = 0;
			Float total = Float.valueOf(NumberUtils.FLOAT_ZERO);
			while (rs.next()) {

				total += rs.getFloat(1);
				rowCount++;
			}

			if (rowCount > 0) {
				ret = total / rowCount;
			}

		} catch (SQLException e) {

			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			HomeFinanceRunner.closeConnection(c, p, rs);
		}

		return ret;
	}

	public Account findByName(final String name) {
		return findByName(name, null);
	}

	public Account findByName(final String name, final String password) {

		Account acct = null;
		String acctName = name;

		// look in cache first
		Cache cache = HomeFinanceRunner.getCache(CACHE_NAME);
		Element element = cache.get(acctName);

		if (null == element) {

			Connection c = null;
			PreparedStatement p = null;
			ResultSet rs = null;

			try {
				c = HomeFinanceRunner.getConnection();

				p = c.prepareStatement(SELECT_COLUMNS + " where name = ?");

				// If DB encrypted then encrypt the name
				Site site = Site.findSite();
				if (null != site && site.isDbEncrypted()
						&& StringUtils.isNotEmpty(password)) {
					BasicTextEncryptor textEncryptor = new BasicTextEncryptor();
					textEncryptor.setPassword(password);

					acctName = textEncryptor.encrypt(acctName);
				}

				p.setString(1, acctName);

				rs = p.executeQuery();

				while (rs.next()) {

					acct = createAccount(rs);

					cache.put(new Element(acct.getName(), acct));
				}

			} catch (SQLException e) {

				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				HomeFinanceRunner.closeConnection(c, p, rs);
			}
		} else {
			acct = (Account) element.getObjectValue();
			// Find out if this account can be refreshed
			acct.setCanRefresh(determineIfCanRefresh(acct));
		}
		return acct;
	}

	public List<Account> findByType(final AccountType at) {

		List<Account> accts = null;

		// look in cache first
		Cache cache = HomeFinanceRunner.getCache(CACHE_NAME);
		Element element = cache.get(at);

		if (null == element) {
			accts = new ArrayList<Account>();
			Account acct = null;

			Connection c = null;
			PreparedStatement p = null;
			ResultSet rs = null;

			try {
				c = HomeFinanceRunner.getConnection();
				p = c.prepareStatement(SELECT_COLUMNS
						+ " where account_type = ?");
				p.setInt(1, at.getId());
				rs = p.executeQuery();

				while (rs.next()) {
					acct = createAccount(rs);
					accts.add(acct);
				}

				cache.put(new Element(at, accts));

			} catch (SQLException e) {

				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				HomeFinanceRunner.closeConnection(c, p, rs);
			}
		} else {
			accts = (List<Account>) element.getObjectValue();
			for (Account account : accts) {
				// Find out if this account can be refreshed
				account.setCanRefresh(determineIfCanRefresh(account));
			}
		}

		return accts;
	}

	protected Account createAccount(final ResultSet rs) throws SQLException {

		Long accountNum = null;

		// If site is encrypted, decrypt the values
		Site site = Site.findSite();
		if (site.isDbEncrypted()) {
			String accountNumStr = rs.getString(COLUMN_ACCOUNT_NUMBER);
			BasicTextEncryptor textEncryptor = new BasicTextEncryptor();
			textEncryptor.setPassword(site.getSiteKey());
			if (StringUtils.isNotEmpty(accountNumStr)) {
				String key = textEncryptor.decrypt(accountNumStr);
				accountNum = Long.valueOf(key);
			} else {
				accountNum = Long.valueOf(0);
			}
		} else {
			accountNum = Long.valueOf(rs.getString(COLUMN_ACCOUNT_NUMBER));
		}

		Integer balanceId = rs.getInt(COLUMN_BALANCE_ID);
		AccountBalance ab = findAccountBalanceById(balanceId);

		Account acct = new Account(rs.getInt(COLUMN_ID),
				rs.getString(COLUMN_NAME), Institution.getById(rs
						.getInt(COLUMN_INSTITUTION_ID)), accountNum,
				AccountType.findById(rs.getInt(COLUMN_ACCOUNT_TYPE)), ab,
				rs.getInt(COLUMN_SEQUENCE));

		if ("Credit Cards".equals(acct.getType().getName())) {
			acct.setAvailableCredit(rs.getFloat(COLUMN_AVAILABLE_CREDIT));
			acct.setTotalCredit(rs.getFloat(COLUMN_TOTAL_CREDIT));
		}

		acct.setLastupdate(rs.getTimestamp(COLUMN_LASTUPDATE));
		acct.setLastFailureDate(rs.getTimestamp(COLUMN_LAST_FAILURE_DATE));

		acct.setHidden(HomeFinanceRunner.convertIntToBool(rs
				.getInt(COLUMN_ACCOUNT_HIDDEN)));

		// Find out if we can auto refresh this account
		acct.setCanRefresh(determineIfCanRefresh(acct));

		return acct;
	}

	private boolean determineIfCanRefresh(final Account acct) {
		// Refresh only if:
		// A UserAccount record exists and it has a data collector class
		// and we have not encountered a failure in the last X minutes
		// and we have not refreshed in the last x minutes
		UserAccount uAcct = UserAccount.findByName(acct.getName());
		boolean canRefresh = false;
		if (null != uAcct
				&& StringUtils.isNotEmpty(uAcct.getRunClass())
				&& (null == acct.getLastFailureDate() || HomeFinanceRunner
						.minutesBetween(acct.getLastFailureDate(), new Date()) > MINUTES_TO_WAIT_ON_ERROR_REFRESH)
				&& (HomeFinanceRunner.minutesBetween(acct.getLastupdate(),
						new Date()) > MINUTES_TO_WAIT_ON_REFRESH)) {
			canRefresh = true;
		}

		return canRefresh;
	}

	protected final Integer writeAccountBalance(final Account account,
			final Date initDate) {

		Integer ret = null;
		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;

		// Get beginning and end of week dates
		Calendar startWeek = new GregorianCalendar();
		startWeek.setTime(initDate);
		int weekday = startWeek.get(Calendar.DAY_OF_WEEK);
		if (weekday != Calendar.SUNDAY) {
			// calculate how much to add
			// the 2 is the difference between Saturday and Monday
			int daysback = (Calendar.SUNDAY - weekday) % DAYS_IN_WEEK;
			startWeek.add(Calendar.DAY_OF_YEAR, daysback);
		}

		// Get a fresh copy of the account to get its
		// account balance for comparison
		Cache cache = HomeFinanceRunner.getCache(AccountDao.CACHE_NAME);
		cache.remove(account.getName());
		Account freshAccount = findByName(account.getName());
		AccountBalance freshAb = freshAccount.getAccountBalance();
		// Only insert a new row if the
		// balance has changed or if we have not added a new
		// balance this week
		if (!freshAb.getBalance().equals(account.getBalance())
				|| freshAb.getLastUpdate().before(startWeek.getTime())) {
			try {
				c = HomeFinanceRunner.getConnection();
				// Insert into account_balance table
				p = c.prepareStatement(INSERT_ACCOUNT_BALANCE_SQL);
				p.setString(1, account.getName());
				p.setFloat(2, account.getBalance());
				p.executeUpdate();
				c.commit();

				// Get the generated ID
				p = c.prepareStatement("CALL IDENTITY()");
				rs = p.executeQuery();
				rs.next();
				ret = rs.getInt(1);
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				HomeFinanceRunner.closeConnection(c, p, rs);
			}
		} else {
			ret = freshAb.getId();
		}

		return ret;
	}

	protected final AccountBalance findAccountBalanceById(final Integer balId) {
		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;
		AccountBalance ab = null;

		try {
			c = HomeFinanceRunner.getConnection();

			p = c.prepareStatement("select account, balance, date "
					+ "from account_balance where id = ?");
			p.setInt(1, balId);
			rs = p.executeQuery();

			if (rs.next()) {
				ab = new AccountBalance(balId, rs.getFloat(COLUMN_BALANCE),
						rs.getDate(COLUMN_DATE));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			HomeFinanceRunner.closeConnection(c, p, rs);
		}

		return ab;
	}

	public final void writeObject(final Account account) {

		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;

		try {
			c = HomeFinanceRunner.getConnection();
			int balanceId = writeAccountBalance(account, new Date());
			p = c.prepareStatement(UPDATE_SQL_WITH_BALANCE);
			p.setFloat(HomeFinanceRunner.DB_COLUMN_FIRST, account.getBalance());
			p.setFloat(HomeFinanceRunner.DB_COLUMN_SECOND,
					account.getAvailableCredit());
			p.setInt(HomeFinanceRunner.DB_COLUMN_THIRD, balanceId);
			p.setTimestamp(HomeFinanceRunner.DB_COLUMN_FOURTH,
					account.getLastFailureDate());
			p.setInt(HomeFinanceRunner.DB_COLUMN_FIFTH,
					HomeFinanceRunner.convertBoolToInt(account.isHidden()));
			p.setString(HomeFinanceRunner.DB_COLUMN_SIXTH, account.getName());
			p.executeUpdate();
			c.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			HomeFinanceRunner.closeConnection(c, p, rs);
		}
	}
}
